﻿Public MustInherit Class CheckDB

    Protected strConnectionString As String = ""
    Public strError As String = ""

    Public Overridable Function ControllaDB(ByVal inConControlloIniziale As Boolean) As Boolean
        Try
            If Not Controlla(inConControlloIniziale) Then
                Call MostraErrore()
                Call DropDB()
                Return False
            Else
                Return True
            End If
        Catch ex As Exception
            Return False
        End Try
    End Function

    Protected MustOverride Function Controlla(ByVal inConControlloIniziale As Boolean) As Boolean
    Protected MustOverride Sub MostraErrore()
    Protected MustOverride Sub DropDB()

#Region "Gestione Tabelle"

    Protected Function ControlloEsistenzaTabella(ByVal inNameTable As String, Optional ByRef inCn As MySqlConnection = Nothing, Optional ByRef inTrans As MySqlTransaction = Nothing, Optional ByRef outProgressBar As Windows.Forms.ProgressBar = Nothing, Optional ByRef outLabel As Windows.Forms.Label = Nothing)
        Try
            Dim cnConnection As MySqlConnection
            If inCn IsNot Nothing Then
                cnConnection = inCn
            Else
                cnConnection = New MySqlConnection(strConnectionString)
                cnConnection.Open()
                inTrans = cnConnection.BeginTransaction
            End If

            If outLabel IsNot Nothing Then
                outLabel.Text = "Creazione " & inNameTable
                outProgressBar.Value += 1
            End If
            Application.DoEvents()
            Dim intCount As Integer = cnConnection.GetSchema("Tables").Select(String.Format("Table_Name like '{0}'", inNameTable)).Count
            If intCount > 0 Then
                Return True
            Else
                Return CreaTabella(inNameTable, cnConnection, inTrans)
            End If
        Catch ex As Exception
            inTrans.Rollback()
            Return False
        End Try
    End Function

    Public Shared Function TableExists(ByVal inNameTable As String) As Boolean
        Try
            Using cnConnection As New MySqlConnection(gConnectionStringAgenda.ToString)
                cnConnection.Open()
                Dim intCount As Integer = cnConnection.GetSchema("Tables").Select(String.Format("Table_Name like '{0}'", inNameTable)).Count
                If intCount > 0 Then
                    Return True
                Else
                    Return False
                End If
            End Using
        Catch ex As Exception
            Return True
        End Try
    End Function

    Public Shared Function FieldExists(ByVal inNameTable As String, ByVal inNameCampo As String) As Boolean
        Try
            Using cnConnection As New MySqlConnection(gConnectionStringAgenda.ToString)
                cnConnection.Open()
                Dim dtTable As DataTable = cnConnection.GetSchema("Columns", {Nothing, Nothing, inNameTable, Nothing})
                If dtTable IsNot Nothing Then
                    If dtTable.Select(String.Format("COLUMN_NAME='{0}'", inNameCampo)).Count > 0 Then
                        Return True
                    Else
                        Return False
                    End If
                Else
                    Return True
                End If
            End Using
        Catch ex As Exception
            Return True
        End Try
    End Function

    Protected MustOverride Function CreaTabella(ByVal inNameTable As String, ByRef inCn As MySqlConnection, ByRef inTrans As MySqlTransaction) As Boolean

#End Region

#Region "Gestione Campi"

    Protected Function ControlloEsistenzaCampo(ByVal inNameTable As String, ByVal inNameCampo As String, Optional ByVal inCn As MySqlConnection = Nothing, Optional ByVal inTrans As MySqlTransaction = Nothing)
        Try
            Dim cnConnection As MySqlConnection
            If inCn IsNot Nothing Then
                cnConnection = inCn
            Else
                cnConnection = New MySqlConnection(strConnectionString)
                cnConnection.Open()
                inTrans = cnConnection.BeginTransaction
            End If

            Using cmCommand As New MySqlCommand("Select * From " & inNameTable, cnConnection)
                Dim dtTable As DataTable = cmCommand.ExecuteReader(CommandBehavior.SchemaOnly Or CommandBehavior.KeyInfo).GetSchemaTable
                If dtTable.Select(String.Format("ColumnName like '{0}'", inNameCampo)).Count > 0 Then
                    Return True
                Else
                    Call CreaCampo(inNameTable, inNameCampo, inCn, inTrans)
                    Return False
                End If
            End Using
        Catch ex As Exception
            inTrans.Rollback()
            Return False
        End Try
    End Function

    Protected Function CreaCampo(ByVal inNameTable As String, ByVal inNameCampo As String, ByRef inCn As MySqlConnection, ByRef inTrans As MySqlTransaction) As Boolean
        Try
            Return True
        Catch ex As Exception
            inTrans.Rollback()
            strError = ex.Message
            Return False
        End Try
    End Function

#End Region

End Class
